--SQL FOR DATABASE
--create database revoelectric
--you might have to change the path based on the files location on your computer
create database revoelectric on (name = revoelectric_mdf, filename = 'G:\year4sem1\advanced databases\ourelectric\database\revoelectric.mdf', size = 2MB,maxsize = 50MB, filegrowth = 1MB) 
log on (name = revoelectric_log, filename = 'G:\year4sem1\advanced databases\ourelectric\database\revoelectric.ldf', size = 2MB,maxsize = 50MB, filegrowth = 1MB);
go

--attached the databse and its log file if they exist 
--you might have to change the path based on the files location on your computer
create database revoelectric on (name = revoelectric_mdf, filename = 'G:\year4sem1\advanced databases\ourelectric\database\revoelectric.mdf'),(name = revoelectric_log, filename = 'G:\year4sem1\advanced databases\ourelectric\database\revoelectric.ldf') 
for attach;
go


/*--------------------------------------TABLES--------------------------------------------*/

--SQL FOR TABLES
create table customer
(
id int IDENTITY(1,1),
first_name varchar(40) not null,
last_name varchar(40) not null,
trn int not null,
street varchar(50) not null,
province varchar(40),
city varchar(40) not null,
country varchar(40) not null default N'Jamaica',
email varchar(100),
contact_num varchar(11) not null,
constraint pk_customer_id primary key (id),
constraint unq_customer_trn unique(trn)
);
go

create table premise
(
id int IDENTITY(1,1),
street varchar(30) not null,
province varchar(30),
city varchar(30) not null,
country varchar(30) default N'Jamaica',
constraint pk_primise_id primary key (id)
);
go

create table meter
(
id int IDENTITY(1,1),
install_date datetime not null,
model varchar(10) not null,
premise_id int not null,
constraint pk_meter_id primary key (id),
constraint fk_meter_premise_id foreign key (premise_id) references premise(id) on delete no action on update cascade
);
go

create table customer_account
(
customer_id int not null,
meter_id int not null,
constraint pk_customer_account primary key (meter_id),
constraint fk_customer_acccount_customer_id foreign key (customer_id) references customer(id) on update cascade,
constraint fk_customer_acccount_meter_id foreign key (meter_id) references meter(id) on update cascade 
);
go

create table billing
(
id int IDENTITY(1,1),
cycle_start datetime not null,
cycle_end datetime,
consumption float(11),
due_date datetime,
current_charge float(11),
current_balance float(11) default 0,
previous_balance float(11) default 0,
total_charge float(11),
tax_charge float(11),
meter_id int not null,
date_generated datetime,
constraint pk_billing_id primary key (id),
constraint fk_billing_meter_id foreign key(meter_id) references meter(id)
);
go

create table payment
(
id int IDENTITY(1,1),
amount float(11) not null,
method varchar(40) not null,
pay_date datetime not null,
paid_by varchar(81) not null,
meter_id int not null,
constraint pk_payment_id primary key(id),
constraint fk_payment_billing_id foreign key (meter_id) references meter(id)
);
go

/*------------------------------------------------VIEWS----------------------------------------*/

--SQL FOR VIEWS

--ALL METERS ON PREMISE
--BASE TABLE: meter, premise
create view premise_meters as select m.*, p.street, p.city from meter m INNER JOIN premise p on m.premise_id=p.id 
go

--PREMISES IN kingston
--BASE TABLE: premise
create view kingston_premises as select * from premise where province='kingston'
go

--OVERDUE BILLS
--BASE TABLE: billing
create view overdue_bills as select * from billing where  due_date < getdate() and current_balance > 0
go

--ALL CURRENT BILLS
--BASE TABLE: billing
create view getAllCurrentBills as select * from billing date_generated is not null and  


/*--------------------------------------------FUNCTIONS--------------------------------------------*/

-- FUNCTION NAME: udf_currentCharge
-- PURPOSE: Calculates a bill's current charge and tax
-- BASE TABLE: billing
-- INPUT VARIABLES: @consumption
-- OUTPUT: table(current_charge, tax)
-- SQL:
CREATE FUNCTION udf_currentCharge(@consumption float)
RETURNS float
AS
BEGIN
	--JMD $100/KWH
	declare @currentCharge float
	set @currentCharge = 100 * @consumption

	return @currentCharge
END
go


/*--------------------------------------------STORED PROCEDURE--------------------------------------------*/

-- PROCEDURE NAME: updateCustomerName
-- PURPOSE: Updates a customer's first and last names
-- BASE TABLE: customer
-- INPUT VARIABLES: @id, @first_name, @last_name
-- SQL:
create procedure sp_updateCustomerName 
@id int, 
@first_name varchar(25), 
@last_name varchar(25)
as
begin
	update customer set first_name=@first_name, last_name=@last_name where id=@id
end
go


-- PROCEDURE NAME: insertNewMeter
-- PURPOSE: insert a new meter record
-- BASE TABLE: customer
-- INPUT VARIABLES: @id, @first_name, @last_name
-- SQL:
create procedure sp_insertNewMeter
@install_date datetime, 
@model varchar(10), 
@premise_id int
as
begin
	declare @result int = 0
	insert into meter (install_date,model,premise_id) values(@install_date,@model,@premise_id)
	if @@ROWCOUNT <> 1
	begin
		set @result = -1
	end
	return @result
end
go


-- PROCEDURE NAME: insertCustomer
-- PURPOSE: inserts a new cuctomer
-- BASE TABLE: customer
-- INPUT VARIABLES: @first_name varchar(40), @last_name varchar(40), @trn int,@street varchar(50),@province varchar(40),@city varchar(40),@country varchar(40),@email varchar(100),@contact_num varchar(11)
-- SQL:
create procedure sp_insertCustomer 
@first_name varchar(40), 
@last_name varchar(40), 
@trn int,
@street varchar(50),
@province varchar(40),
@city varchar(40),
@country varchar(40),
@email varchar(100),
@contact_num varchar(11)
as
begin
	declare @status int = 0
	insert into customer (first_name,last_name,trn,street,province,city,country,email,contact_num)
	values(@first_name,@last_name,@trn,@street,@province,@city,@country,@email,@contact_num)
	if @@ROWCOUNT = 1
	begin
		set @status = 1 --customer record was inserted
	end
	else
	begin
		set @status = -1  --customer record was NOT inserted
	end
	return @status
end
go



-- PROCEDURE NAME: updateCustomer
-- PURPOSE: updates an existing customer record
-- BASE TABLE: customer
-- INPUT VARIABLES: @first_name varchar(40), @last_name varchar(40), @trn int,@street varchar(50),@province varchar(40),@city varchar(40),@country varchar(40),@email varchar(100),@contact_num varchar(11), @id int
-- SQL:
create procedure sp_updateCustomer 
@first_name varchar(40), 
@last_name varchar(40), 
@trn int,
@street varchar(50),
@province varchar(40),
@city varchar(40),
@country varchar(40),
@email varchar(100),
@contact_num varchar(11),
@id int
as
begin
	declare @status int = 0
	
	update customer 
	set 
	first_name = @first_name,last_name = @last_name,trn = @trn,street = @street,province = @province,
	city = @city,country = @country,email = @email,contact_num = @contact_num
	where id = @id
	
	if @@ROWCOUNT = 1
	begin
		set @status = 1 --customer record was updated
	end
	else
	begin
		set @status = -1  --customer record was NOT updated
	end
	return @status
end
go



-- PROCEDURE NAME: sp_getAllCustomers
-- PURPOSE: gets all customers
-- BASE TABLE: customer
-- INPUT VARIABLES: 
-- SQL:
create procedure sp_getAllCustomers 
as
begin
	select * from customer
end



-- PROCEDURE NAME: sp_createCustomerAccount
-- PURPOSE: Creates customer account by assignming a meter to a customer start their billing cycle
-- BASE TABLE: customer_account, billing
-- INPUT VARIABLES: @customerId, @meterId
-- SQL:
create procedure sp_createCustomerAccountByID @customerId int, @meterId int 
as
begin
	declare @result int = 0
	begin transaction tran1
	
	insert into customer_account (meter_id, customer_id) values(@meterId, @customerId)
	if @@ROWCOUNT = 1 
	begin 
		insert into billing (cycle_start,meter_id) values(getdate(),@meterId)
		if @@ROWCOUNT = 1
		begin
			commit transaction tran1
			set @result = 1 --procedured succeeded
		end
		else
		begin
			rollback transaction tran1
			set @result = -2 --error inserting billing record
		end
	end
	else
	begin
		rollback transaction tran1
		set @result = -1 --error inserting customer_account record
	end
	return @result
end
go


-- PROCEDURE NAME: sp_createCustomerAccount
-- PURPOSE: Creates customer account by assignming a meter to a customer start their billing cycle
-- BASE TABLE: customer, customer_account, billing
-- INPUT VARIABLES: @trn, @meterId
-- SQL:
create procedure sp_createCustomerAccountByTrn @trn int, @meterId int 
as
begin
	declare @result int = 0, @customerId int
	begin transaction tran1
	if exists (select id from customer where trn = @trn)
	begin
	
		select @customerId = id from customer where trn = @trn
		
		insert into customer_account (meter_id, customer_id) values(@meterId, @customerId)
		if @@ROWCOUNT = 1 
		begin 
			insert into billing (cycle_start,meter_id) values(getdate(),@meterId)
			if @@ROWCOUNT = 1
			begin
				commit transaction tran1
				set @result = 1 --procedured succeeded
			end
			else
			begin
				rollback transaction tran1
				set @result = -2 --error inserting billing record
			end
		end
		else
		begin
			rollback transaction tran1
			set @result = -1 --error inserting customer_account record
		end
	end
	else
	begin
		set @result = -3 --cannot find customer with specified trn 
	end
	return @result
end
go



-- PROCEDURE NAME: sp_updateCustomerAccount
-- PURPOSE: updates a customer account by updated the customer id column 
-- BASE TABLE: customer_account
-- INPUT VARIABLES: @customerId, @meterId
-- SQL:
create procedure sp_updateCustomerAccount @customer_id int, @meter_id int, @result int OUT
as
begin
	set @result = 0
	update customer_account set customer_id = @customer_id where meter_id = @meter_id
	
	if @@ROWCOUNT = 1
	begin
		set @result = 1
	end
	else
	begin
		set @result = -1
	end
end



-- PROCEDURE NAME: sp_getAllAccounts
-- PURPOSE: Gets all customer accounts
-- BASE TABLE: customer, customer_account
-- INPUT VARIABLES: 
-- SQL:
create procedure sp_getAllAccounts
as
begin
	select distinct ca.customer_id, c.trn, c.first_name, c.last_name, ca.meter_id from customer c inner join customer_account ca on c.id = ca.customer_id 	
end
go



-- PROCEDURE NAME: sp_updateBillingConsumption
-- PURPOSE: Gets all customer accounts
-- BASE TABLE: customer, customer_account
-- INPUT VARIABLES: 
-- SQL:
create procedure sp_updateBillingConsumption @consumption float, @id int
as 
begin
	declare @status int = 0
	
	update billing set consumption = @consumption where id = @id
	
	if @@TRANCOUNT = 1
	begin
		set @status = 1
	end
	else
	begin
		set @status = -1
	end
	return @status
end




-- PROCEDURE NAME: sp_getAllUngeneratedBills
-- PURPOSE: Gets all ungenerated bills
-- BASE TABLE: billing
-- INPUT VARIABLES: 
-- SQL:
create procedure sp_getAllUngeneratedBills  
as
begin
	select c.id as customer_id, c.trn, c.first_name, c.last_name, b.id as billing_id,b.meter_id 
	from customer c 
	inner join customer_account ca on c.id = ca.customer_id 
	inner join billing b on ca.meter_id = b.meter_id where b.date_generated is null
end
go


-- PROCEDURE NAME: sp_getCurrentUngeneratedBills
-- PURPOSE: Gets current ungenerated bills
-- BASE TABLE: billing
-- INPUT VARIABLES: 
-- SQL:
create procedure sp_getCurrentUngeneratedBills 
as
begin
	select b.*
	from customer c 
	inner join customer_account ca on c.id = ca.customer_id 
	inner join billing b on ca.meter_id = b.meter_id where b.date_generated is null and datediff(hour,b.cycle_end, getdate())> 0
end
go



-- PROCEDURE NAME: sp_getAllFromBilling
-- PURPOSE: gets all bills 
-- BASE TABLE: billing
-- INPUT VARIABLES: 
-- SQL:
create procedure sp_getAllFromBilling
as
begin
	select * from dbo.getAllCurrentBills
end
go



-- PROCEDURE NAME: sp_getCustomerMeterByAmount
-- PURPOSE: Gets all cutsomers with more than x and less than y meters/account
-- BASE TABLE: customer_account
-- INPUT VARIABLES: @minAmount, @maxAmount
-- SQL:
create procedure sp_getCustomerMeterByAmount @minAmount int, @maxAmount int
as
begin
	declare @customerId int, @meterCount int = 0
	
	declare @resultSet table 
	(
		customer_id int,
		meter_count int
	)
	
	declare customerMeter_cursor cursor 
	for select distinct c.id 
	from customer c 
	inner join customer_account ca on c.id = ca.customer_id order by (c.id) asc
	
	open customerMeter_cursor
	fetch next from customerMeter_cursor into @customerId
	
	while @@FETCH_STATUS = 0
	begin
		select @meterCount = count(*) from customer_account where customer_id = @customerId group by(customer_id)
		if @meterCount >= @minAmount AND @meterCount <=@maxAmount
		begin
			insert into @resultSet (customer_id,meter_count) values(@customerId,@meterCount)
		end
		fetch next from customerMeter_cursor into @customerId
	end
	
	close customerMeter_cursor
	deallocate customerMeter_cursor
	
	select c.id as customer_id, c.trn, c.first_name, c.last_name, c.street, c.city, c.province, rs.meter_count
	from customer c inner join @resultSet as rs on c.id = rs.customer_id order by rs.meter_count desc
end
go


-- PROCEDURE NAME: sp_getOutstandingCustomersByParish
-- PURPOSE: Gets all customers with balance greater or equal amount in parish y
-- BASE TABLE: Premise, Billing, Payment
-- INPUT VARIABLES: @outstandingAmountMin, @outstandingAmountMax, @province
-- SQL:
create procedure sp_getOutstandingCustomersByParish @outstandingAmountMin float, @outstandingAmountMax float, @province varchar(40)
as
begin
	declare @billingMeterId int, @resultBillingId int
	declare @resultSet table 
	(
		billing_id int
	)
	declare billingMeter_cursor cursor 
	for select meter_id 
	from billing where datediff(hour, due_date,getdate()) > 0 and current_balance >= @outstandingAmountMin and current_balance <= @outstandingAmountMax and date_generated is not null group by(meter_id)
	
	open billingMeter_cursor
	fetch next from billingMeter_cursor into @billingMeterId
	
	while @@FETCH_STATUS = 0
	begin
		if exists (select top 1 id from billing where meter_id = @billingMeterId and datediff(hour, due_date,getdate()) > 0 order by cycle_start desc)
		begin
			select top 1 @resultBillingId = id from billing where meter_id = @billingMeterId and datediff(hour, due_date,getdate()) > 0 order by cycle_start desc
			insert into @resultSet (billing_id) values(@resultBillingId)
		end
		fetch next from billingMeter_cursor into @billingMeterId
	end
	
	close billingMeter_cursor
	deallocate billingMeter_cursor
	
	select c.id as customer_id, c.trn, c.first_name, c.last_name, meterResultSet.id as billing_id, meterResultSet.meter_id as meter_id, meterResultSet.current_balance as overdue_amount, meterResultSet.due_date as overdue_since, p.province as premise_parish
	from customer c 
	inner join customer_account ca on c.id = ca.customer_id 
	inner join (select b.meter_id, b.id, b.current_balance,b.due_date from @resultSet rs inner join billing b on b.id = rs.billing_id) meterResultSet on meterResultSet.meter_id = ca.meter_id 
	inner join meter m on meterResultSet.meter_id = m.id 
	inner join premise p on m.premise_id = p.id 
	where p.province = @province order by meterResultSet.current_balance desc
end
go




-- PROCEDURE NAME: sp_getOverduedBillsByYear
-- PURPOSE: Gets a customer and theirs over dued bills in a specific year
-- BASE TABLE: billing, customer
-- INPUT VARIABLES: @customerId, @year
-- SQL:
create procedure sp_getOverduedBillsByYear @customerTrn int, @year int
as
begin
	declare @yearPart int 
	select c.trn, c.first_name, c.last_name, b.current_balance, b.date_generated, b.due_date as overdue_since from customer c inner join customer_account ca on c.id = ca.customer_id  inner join meter m  on ca.meter_id = m.id
	inner join billing b on m.id = b.meter_id where c.trn = @customerTrn and  b.current_balance > 0 and datediff(hour,b.due_date,getdate()) > 0 and  datepart(year,date_generated) = @year 
end
go


-- PROCEDURE NAME: sp_insertPayment
-- PURPOSE: inserts a new payment record
-- BASE TABLE: payment
-- INPUT VARIABLES: @amount, @method, @paid_by, @meter_id
-- SQL:
create procedure sp_insertPayment @amount float, @method varchar(40), @paid_by varchar(81), @meter_id int 
as
begin
	begin try
		insert into payment (amount, method, paid_by,meter_id, pay_date) values (@amount, @method, @paid_by, @meter_id, getdate())
	end try
	begin catch 
		return -1
	end catch
	return 1
end



-- PROCEDURE NAME: sp_getAllPayments
-- PURPOSE: gets all payments ever made to an account
-- BASE TABLE: payment
-- INPUT VARIABLES: 
-- SQL:
create procedure sp_getAllPayments 
as
begin
	select * from payment
end



/*--------------------------------------------TRIGGERS--------------------------------------------*/ 
 
--TRIGGER NAME: adjustDates
--ACTION: insert billing record
--REACTION: adjust the billing cycle end date and billing due date 
--PURPOSE: To ensure that the corret billing cycle end date and billing due date are inserted.
create trigger adjustDates
on billing
after insert
as
begin
	declare @id int
	declare @cycle_start datetime
	declare @cycle_end datetime
	declare @due_date datetime

	select @id=id, @cycle_start=cycle_start from inserted

	set @cycle_end = dateadd(day,30,@cycle_start);
	set @due_date = dateadd(day,15,@cycle_end);

	update billing set cycle_end=@cycle_end, due_date=@due_date where id=@id
end
go


--TRIGGER NAME: updateBillBalanceOnBillPayment
--ACTION: insert payment record
--REACTION: update billing previous balance,
--PURPOSE: To update the billing current balance when a payement is made.
create trigger updateBillBalanceOnBillPayment
on payment
after insert
as
begin
	declare @meterId int, @paymentAmount float, @billingAmount float, @amount float, @billingId int, @totalCharge float
	
	select @meterId = meter_id, @paymentAmount = amount from inserted

	if exists (select top 1 id from billing where meter_id = @meterId and datediff(hour,due_date,getdate())<0 and date_generated is not null order by cycle_start desc)
	begin 
		select @amount=current_balance, @billingId = id, @totalCharge = total_charge
		from billing 
		where id = (select top 1 id from billing where meter_id = @meterId and datediff(hour,due_date,getdate())<0 and date_generated is not null order by cycle_start desc)	
	end
	else
	begin
		if exists (select top 1 id from billing where meter_id = @meterId order by cycle_start desc)
		begin
			select @amount=current_balance, @billingId = id, @totalCharge = total_charge
			from billing 
			where id =	(select top 1 id from billing where meter_id = @meterId order by cycle_start desc)
			if @totalCharge is null
			begin
				set @totalCharge = 0
			end
			else
			begin
				set @totalCharge = @totalCharge - @paymentAmount
			end
		end
	end

	update billing set current_balance = (@amount-@paymentAmount), total_charge = @totalCharge where id = @billingId
	if @@ROWCOUNT <> 1
	begin
		raiserror ('Bill Payment Failed. Cannot Update billing record.',12,2)
		rollback
	end
end
go 


--TRIGGER NAME: createNewBillOnDateGenerated
--ACTION: update of the date_generated field
--REACTION: create a billing record, inserting cycle start 
--PURPOSE: To created a new billing record for a user once their current billing has been gereated
create trigger createNewBillOnDateGenerated 
on billing 
for update 
as
begin 
	if update(date_generated)
	begin
		declare @lastBillingCycleEnd datetime,@meterId int,@previousBalance float, @currentCharge float, @currentBalance float
		
		select @meterId = meter_id, @currentCharge = current_charge, @currentBalance = current_balance from inserted
		select top 1 @lastBillingCycleEnd = cycle_end, @previousBalance = current_balance from billing where meter_id = @meterId order by cycle_end desc
		
		if datediff(hour,@lastBillingCycleEnd,getdate()) > 0 
		begin
			if @currentBalance is not null and @currentCharge is not null
			begin
				insert into billing (cycle_start, meter_id, previous_balance,current_balance) values(@lastBillingCycleEnd, @meterId, @previousBalance,@previousBalance)
				
				if @@ROWCOUNT <> 1
				begin
					raiserror ('Update Failed. There was an error creating a new billing record.',12,2)
					rollback
				end
			end
			else
			begin
				raiserror ('Update Failed. Cannot create a new bill record because the current bill charge or current balance cannot be null',12,2)
				rollback 
			end
		end
		else
		begin
			raiserror ('Update Failed. Cannot create a new bill record before the current bill cycle ends.',12,2)
			rollback 
		end
	end
end
go

  
--TRIGGER NAME: billAmountsOnUpdateConsumption
--ACTION: update of the date_generated field
--REACTION: create a billing record, inserting cycle start 
--PURPOSE: To created a new billing record for a user once their current billing has been gereated
create trigger billAmountsOnUpdateConsumption
on billing 
after update 
as
begin 
	if update(consumption)
	begin
		declare @billingId int, @consumption float, @currentCharge float
		
		select @billingId = id, @consumption = consumption from inserted
		
		set @currentCharge = dbo.udf_currentCharge(@consumption)
		
		update billing set current_charge = @currentCharge, 
		current_balance = @currentCharge + previous_balance, 
		total_charge = @currentCharge + previous_balance
		where id = @billingId
		
		if @@ROWCOUNT <> 1
		begin
			raiserror ('There was an error creating a new employee billing record.',12,2)
			rollback
		end
	end
end
go

  
  
--TRIGGER NAME: preserveCustomer
--ACTION: delete customer record
--REACTION: raise error 
--PURPOSE: To prevent deletion of customer record
create trigger preserveCustomer
on customer
instead of delete
as
begin
	raiserror('We do not allow employees to be deleted.',10,1)
	rollback
end
go
